Imports System.Data

Partial Class _Default
  Inherits System.Web.UI.Page

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Page.IsPostBack = False Then
            Dim blAdv As Boolean = DetUser()
            Today_lbl.Text = "Today is " & MonthName(Month(Date.Now), True) & " " & Day(Date.Now) & ", " & Year(Date.Now)
            DGFill(Bday_dg, "Bday_dg", Bday_lbl, Bday_lbl2, blAdv)
            DGFill(Due_dg, "Due_dg", Due_lbl, Due_lbl2, blAdv)
            DGFill(Delayed_dg, "Delayed_dg", Delayed_lbl, Delayed_lbl2, blAdv)
            DGFill(Maturity_dg, "Maturity_dg", Maturity_lbl, Maturity_lbl2, blAdv)
        End If
  End Sub

  Public Sub DGFill(ByVal objDGrid As DataGrid, ByVal strGrid As String, _
    ByVal Label1 As Label, ByVal Label2 As Label, ByVal advise As Boolean)

    Dim sDS As New Data.DataSet
    Dim sConn As New Data.SqlClient.SqlConnection
    Dim sCom As New Data.SqlClient.SqlCommand
    Dim sDApt As New Data.SqlClient.SqlDataAdapter(sCom)

        Dim strFilter As String = ""

        If Not IsNothing(Session("advisor")) Then
            strFilter = " And Advisor_Cd = '" & Session("UserName") & "'"
        End If

        Select Case strGrid
            Case "Bday_dg"
                sCom.CommandText = "SELECT distinct B.*" & _
                        " FROM vw_rpt_birthday_list B" & _
                        " WHERE dbo.GetBirthDate(B.Birthday) between dateadd(day, -2, GetDate()) And dateadd(day, " & Val(txtBday.Text) & ", GetDate())" & _
                        strFilter & _
                        " Order by dbo.GetBirthDate(B.Birthday)"

            Case "Due_dg"
                sCom.CommandText = "Select distinct *, dbo.fn_GetAdvisorName(policy_seq_cd) Advisor" & _
                        " From vw_policy_premium_dtl" & _
                        " Where policy_due_dt between dateadd(day, -1, GetDate()) And dateadd(day, " & Val(txtDue.Text) & ", GetDate())" & _
                        " And pay_tag = 0" & _
                        strFilter & _
                        " Order by policy_due_dt"

            Case "Delayed_dg"
                sCom.CommandText = "Select distinct *, dbo.fn_GetAdvisorName(policy_seq_cd) Advisor" & _
                        " From vw_policy_premium_dtl" & _
                        " Where policy_due_dt < GetDate()" & _
                        " And pay_tag = 0" & _
                        strFilter & _
                        " Order by policy_due_dt"

            Case "Maturity_dg"
                sCom.CommandText = "Select distinct *" & _
                        " From vw_policy_maturity" & _
                        " Where maturity_dt between dateadd(day, -1, GetDate()) And dateadd(day, " & Val(txtMaturity.Text) & ", GetDate())" & _
                        strFilter & _
                        " Order by maturity_dt"

        End Select

        Try
            'sConn.ConnectionString = "Data Source=HEAVEN;Initial Catalog=CIMS;Integrated Security=True"
            sConn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("CnnStr")
            sCom.CommandType = Data.CommandType.Text
            sCom.Connection = sConn
            sConn.Open()
            sDApt.Fill(sDS)

            If sDS.Tables(0).Rows.Count > 0 Then
                With objDGrid
                    .DataSource = sDS
                    .DataBind()
                    .Visible = True
                End With
            Else
                Label1.Visible = True : Label2.Visible = True
                objDGrid.Visible = False
            End If

            sDS = Nothing

        Catch ex As Exception

        Finally
            sConn.Close()
        End Try

    End Sub

  Public Function DetUser() As Boolean
        DetUser = True

        Dim dtUser As New DataTable
        Dim sConn As New Data.SqlClient.SqlConnection
        Dim sCom As New Data.SqlClient.SqlCommand
        Dim sDApt As New Data.SqlClient.SqlDataAdapter(sCom)

        Try
            'sConn.ConnectionString = "Data Source=HEAVEN;Initial Catalog=CIMS;Integrated Security=True"
            sConn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("CnnStr")
            sCom.CommandType = Data.CommandType.Text
            sCom.Connection = sConn

            sCom.CommandText = "SELECT * FROM user_mstr " & _
              "WHERE (user_name = N'" & Session("UserName") & "')"

            sConn.Open()
            sDApt.Fill(dtUser)

            If dtUser.Rows.Count > 0 Then
                If dtUser.Rows(0).Item("advisor_tag").ToString = "-1" Then
                    Bday_dg.Columns(3).Visible = False
                    Due_dg.Columns(4).Visible = False
                    Delayed_dg.Columns(4).Visible = False
                    Maturity_dg.Columns(4).Visible = False
                    DetUser = False
                End If
                User_lbl.Text = dtUser.Rows(0).Item("user_first_name").ToString.ToUpper & " " & _
                  dtUser.Rows(0).Item("user_last_name").ToString.ToUpper
            End If

        Catch ex As Exception

        Finally
            sConn.Close()
        End Try

  End Function

    Protected Sub btnGo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBDay.Click, btnhDue.Click, btnMaturity.Click
        Dim blAdv As Boolean = DetUser()

        DGFill(Bday_dg, "Bday_dg", Bday_lbl, Bday_lbl2, blAdv)
        DGFill(Due_dg, "Due_dg", Due_lbl, Due_lbl2, blAdv)
        DGFill(Maturity_dg, "Maturity_dg", Maturity_lbl, Maturity_lbl2, blAdv)
    End Sub
End Class